//
//  DataBaseSingleton.m
//  豆瓣
//
//  Created by dllo on 15/11/4.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseSingleton.h"
#import <sqlite3.h>
#import "Activity.h"


@implementation DataBaseSingleton

+(instancetype)shareDataBase{
    
    static DataBaseSingleton *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseSingleton alloc]init];
        
    }
    return dataBase;
}

static sqlite3 *db = nil;

-(void)openDB{
    if (db != nil) {
        NSLog(@"数据库已打开");
        return;
    }
    
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqlPath = [sqlFile stringByAppendingPathComponent:@"douban.sqlite"];
    NSLog(@"======%@", sqlPath);
    sqlite3_open(sqlPath.UTF8String, &db);
    
}

- (void)createActivityListTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activityList(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, begin_time TEXT, adderss TEXT, participant_count TEXT, wisher_count TEXT, category_name TEXT, image TEXT, content TEXT, name TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
}


- (void)insertActivityListSQL:(Activity *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activityList(title, begin_time, adderss, participant_count, wisher_count, category_name, image, content, name) VALUES('%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@', '%@')", activity.title, activity.begin_time, activity.address, activity.participant_count, activity.wisher_count, activity.category_name, activity.image, activity.content, activity.name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
}

- (void)dropTable{
    NSString *sql = @"DROP TABLE activityList";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
}


- (NSMutableArray *)selectActivityList{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activityList"];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
        const unsigned char *address = sqlite3_column_text(stmt, 3);
        const unsigned char *participant_count = sqlite3_column_text(stmt, 4);
        const unsigned char *wisher_count = sqlite3_column_text(stmt, 5);
        const unsigned char *category_name = sqlite3_column_text(stmt, 6);
        const unsigned char *image = sqlite3_column_text(stmt, 7);
        const unsigned char *content = sqlite3_column_text(stmt, 8);
        const unsigned char *name = sqlite3_column_text(stmt, 9);
        
        NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
        NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
        NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
        NSString *participant_countStr = [NSString stringWithUTF8String:(const char *)participant_count];
        NSString *wisher_countStr = [NSString stringWithUTF8String:(const char *)wisher_count];
        NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
        NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
        NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
        
        
        Activity *act = [[Activity alloc]init];
        
        act.title = titleStr;
        act.begin_time = begin_timeStr;
        act.address = addressStr;
        act.participant_count = participant_countStr;
        act.wisher_count = wisher_countStr;
        act.category_name = category_nameStr;
        act.image = imageStr;
        act.content = contentStr;
        act.name = nameStr;
        
        [array addObject:act];
    }
    return array;
    
}



- (void)createUserListTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS userList(number INTEGER PRIMARY KEY AUTOINCREMENT, user TEXT, passwd TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }
}

- (void)insertUserList:(User *)user{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO userList(user, passwd) VALUES('%@', '%@')", user.user, user.passwd];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
    }
}

- (NSMutableArray *)selectUserList
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM userList"];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *user = sqlite3_column_text(stmt, 1);
        const unsigned char *passwd = sqlite3_column_text(stmt, 2);
        
        NSString *userStr = [NSString stringWithUTF8String:(const char *)user];
        NSString *passwdStr = [NSString stringWithUTF8String:(const char *)passwd];
        
        User *use = [[User alloc] init];
        
        use.user = userStr;
        use.passwd = passwdStr;
        
        [array addObject:use];
    }
    return array;
}

- (NSMutableArray *)selectUserName
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM userList"];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *user = sqlite3_column_text(stmt, 1);
        
        NSString *userStr = [NSString stringWithUTF8String:(const char *)user];
        
        [array addObject:userStr];
    }
    return array;
}

- (NSMutableArray *)selectUserPasswd
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM userList"];
    sqlite3_stmt *stmt = nil;
    sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *passwd = sqlite3_column_text(stmt, 2);
        
        NSString *passwdStr = [NSString stringWithUTF8String:(const char *)passwd];
        
        [array addObject:passwdStr];
    }
    return array;
}

- (NSMutableArray *)selectActivityList:(NSString *)title
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activityList WHERE title = '%@'", title];
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }else{
        NSLog(@"查询准备失败");
    }
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        const unsigned char *begin_time = sqlite3_column_text(stmt, 2);
        const unsigned char *address = sqlite3_column_text(stmt, 3);
        const unsigned char *participant_count = sqlite3_column_text(stmt, 4);
        const unsigned char *wisher_count = sqlite3_column_text(stmt, 5);
        const unsigned char *category_name = sqlite3_column_text(stmt, 6);
        const unsigned char *image = sqlite3_column_text(stmt, 7);
        const unsigned char *content = sqlite3_column_text(stmt, 8);
        const unsigned char *name = sqlite3_column_text(stmt, 9);
        
        NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
        NSString *begin_timeStr = [NSString stringWithUTF8String:(const char *)begin_time];
        NSString *addressStr = [NSString stringWithUTF8String:(const char *)address];
        NSString *participant_countStr = [NSString stringWithUTF8String:(const char *)participant_count];
        NSString *wisher_countStr = [NSString stringWithUTF8String:(const char *)wisher_count];
        NSString *category_nameStr = [NSString stringWithUTF8String:(const char *)category_name];
        NSString *imageStr = [NSString stringWithUTF8String:(const char *)image];
        NSString *contentStr = [NSString stringWithUTF8String:(const char *)content];
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)name];
        
        
        Activity *act = [[Activity alloc]init];
        
        act.title = titleStr;
        act.begin_time = begin_timeStr;
        act.address = addressStr;
        act.participant_count = participant_countStr;
        act.wisher_count = wisher_countStr;
        act.category_name = category_nameStr;
        act.image = imageStr;
        act.content = contentStr;
        act.name = nameStr;
        
        [array addObject:act];

        
    }
    return array;
}


- (NSMutableArray *)selectMovieName:(NSString *)UserName
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM collectList WHERE userName = '%@'", UserName];
    sqlite3_stmt *stmt = nil;
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }else{
        NSLog(@"查询准备失败");
    }
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        NSString *titleStr = [NSString stringWithUTF8String:(const char *)title];
        
        [array addObject:titleStr];
        
    }
    return array;
}

- (void)createCollectListTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS collectList(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, userName TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    NSLog(@"%d", result);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
    }else{
        NSLog(@"创建表失败");
    }
}

-(void)insertCollectList:(NSString *)title :(NSString *)name
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO collectList(title, userName) VALUES('%@', '%@')", title ,name];
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"收藏成功");
    }
    else
    {
        NSLog(@"收藏失败");
    }
}

- (void)deleteCollect:(NSString *)name :(NSString *)title
{
    NSString *sql = [NSString stringWithFormat:@"DELETE FROM collectList WHERE userName = '%@' and title = '%@' ", name, title];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }
    else{
        NSLog(@"删除失败");
    }
}

@end
